# -*- coding: utf8 -*-

import os
import cx_Oracle
import datetime
import settings
import MySQLdb



''' def OutputTypeHandler(cursor, name, defaultType, size,
        precision, scale): 
    if defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR): 
        return cursor.var(unicode, size, cursor.arraysize)

class BIDbQuery(object):
    """
    jdz oracle 系统
    """

    def __init__(self):
        self.conn = cx_Oracle.connect("jdzpost/jdzpost123@jdzpost")
        self.cursor = self.conn.cursor() '''

def OutputTypeHandler(cursor, name, defaultType, size,
        precision, scale): 
    if defaultType in (cx_Oracle.STRING, cx_Oracle.FIXED_CHAR): 
        return cursor.var(unicode, size, cursor.arraysize)


class BIDbQuery(object):
    """
    访问经营分析系统
    """

    def __init__(self):
       #连 postgres
       # self.conn = psycopg2.connect(settings.PG_DB)
       # self.cursor = self.conn.cursor()

        # 连mysql 腾讯云
        self.conn = MySQLdb.connect("115.159.59.79", "root", "root", "db_crm", charset='utf8' )

        # 连mysql 省虚拟机
        #self.conn = MySQLdb.connect("111.75.223.93", "root", "root", "db_crm", charset='utf8' )
        self.cursor = self.conn.cursor()

        #连oracle
        # self.conn = cx_Oracle.connect("jdzpost/jdzpost123@jdzpost")
        # self.cursor = self.conn.cursor()


    def close(self):
        self.conn.close()

    def get_year(self, dwid):
        '''获取test'''

        self.cursor.execute("select 单位名称，单位人数，开办邮益宝人数，总资产，营销部门,所属区域,归属部门,归属网点 from 单位明细表 where 单位ID = :dwid", {'dwid': dwid})
        results = []
        for r in self.cursor.fetchall():
            d = dict()
            d['dwmc'] = unicode(r[0], 'utf8')
            d['dwrs'] = r[1]
            d['yybrs'] = r[2]
            d['zzc'] = r[3]
            if r[4] == None:
                d['yxbm'] = ''
            else:   
                d['yxbm'] = unicode(r[4], 'utf8')
            
            if r[5] == None:
                d['ssqy'] = ''
            else:   
                d['ssqy'] = unicode(r[5], 'utf8')

            if r[6] == None:
                d['gsbm'] = ''
            else:   
                d['gsbm'] = unicode(r[6], 'utf8')

            if r[7] == None:
                d['gswd'] = ''
            else:   
                d['gswd'] = unicode(r[7], 'utf8')         

            results.append(d)

        return results

    def get_data(self):

        self.cursor.execute("select * from jgql limit 30")
        results = []
        for r in self.cursor.fetchall():
            d = dict()
            d['brh_code'] = r[0]
            d['up_brh_code'] = r[1]
            d['brh_name'] = unicode(r[2], 'utf8')
            results.append(d)
        return results

    def get_company_custom(self,dwid):
        '''获取单位下的人员信息'''
        sql = "SELECT 姓名,身份证号码,营销人员,总资产,手机号码,id FROM db_crm.单位客户信息表 where 单位ID = %s limit 10"

        self.cursor.execute(sql,(dwid,))
        results = []
        for r in self.cursor.fetchall():
            d = dict()
            d['name'] = r[0]
            d['cardid'] = r[1]
            d['phone'] = r[4]
            d['id'] = r[5]
            ''' d['yxry'] = r[2]
            d['total_num'] = str(r[3])
            d['yxkh'] = r[4]
            d['phone'] = r[5]
            d['first_time'] = str(r[6])
            d['first_num'] = r[7] '''
            results.append(d)

        return results

    def get_company_custom_more(self,dwid,rowcount,pagesize):
        '''获取单位下的人员信息'''
        rowcount = int(rowcount)
        pagesize = rowcount + int(pagesize)
        self.cursor.execute("SELECT 姓名,身份证号码,营销人员,总资产,手机号码,id FROM db_crm.单位客户信息表 where 单位ID = %s order by 身份证号码 limit %s,%s",(dwid,rowcount,pagesize))
        results = []
        for r in self.cursor.fetchall():
            d = dict()
            d['name'] = r[0]
            d['cardid'] = r[1]
            d['phone'] = r[4]
            d['id'] = r[5]
            ''' d['yxry'] = r[2]
            d['total_num'] = str(r[3])
            d['yxkh'] = r[4]
            d['phone'] = r[5]
            d['first_time'] = str(r[6])
            d['first_num'] = r[7] '''
            results.append(d)

        return results

    def get_company_serch(self,dwid):
        '''获取单位信息'''

        sql = "SELECT 单位ID,单位名称, 单位人数, 总资产,开办邮益宝人数,归属部门,归属网点 FROM db_crm.单位明细表 where 单位ID = %s "
        self.cursor.execute(sql,(dwid,))
        results = []
        for r in self.cursor.fetchall():
            d = dict()
            d['dwid'] = r[0]
            d['dwmc'] = r[1]
            d['dwrs'] = r[2]
            d['zzc'] = str(r[3])
            d['kbyybrs'] = r[4]
            d['gsbm'] = r[5]
            d['gswd'] = r[6]
            results.append(d)       

        return results

    def get_company_update(self,dwid,dwmc,dwrs,zzc,kbyybrs,gsbm,gswd):
        '''更新单位信息'''

        sql = "UPDATE db_crm.单位明细表 SET 单位名称 = %s , 单位人数 = %s, 总资产 = %s, 开办邮益宝人数 = %s, 归属部门 = %s, 归属网点 = %s WHERE 单位ID = %s"
        self.cursor.execute(sql,(dwmc,dwrs,zzc,kbyybrs,gsbm,gswd,dwid))
        self.conn.commit()

        return 'OK'   


    def get_company_custom_inser(self,cardid,name,phone,dwid,first_num=0):

        '''获取单位下的人员信息'''
        #增加单位人数
        self.cursor.execute("SELECT 单位人数 FROM db_crm.单位明细表 where 单位ID=%s",(dwid,))
        for r in self.cursor.fetchall():
            d = dict()
            d['people_count'] = int(r[0])
        people_count = d['people_count'] + 1

        #判断身份证/手机号是否存在
        phone_exist = self.cursor.execute("SELECT 客户ID FROM db_crm.单位客户信息表 where 手机号码=%s",(phone,))
        cardid_exist = self.cursor.execute("SELECT 客户ID FROM db_crm.单位客户信息表 where 身份证号码=%s",(cardid,))


        if phone_exist > 0 or cardid_exist > 0 :
            return 'false'
        else:
            #插入数据库
            self.cursor.execute("update db_crm.单位明细表 set 单位人数 = %s where 单位ID=%s",(people_count,dwid))
            self.conn.commit()
            #restuts = [{'cardid':cardid},{'name':name},{'SUCCESS':'OK'}]
            sql = "insert into db_crm.单位客户信息表 (姓名,身份证号码,手机号码,首次体验金额,单位ID) values (%s,%s,%s,%s,%s)"
            #self.cursor.execute("insert into db_crm.邮益宝客户数据录入表1 (姓名,身份证号码,手机号码,首次体验金额,单位名称) values ('张10','360109998765423132','13456765873','200','江西景航航空锻铸有限公司')")
            self.cursor.execute(sql,(name,cardid,phone,first_num,dwid))
            self.conn.commit()
            return 'OK'
        

    
    def get_company_custom_update(self,ygid,cardid,name,phone,first_time=00000000,first_num=0):
        '''获取单位下的人员信息'''
        cardid = str(cardid)
        phone = str(phone)
        first_num = str(first_num)

        phone_exist = self.cursor.execute("SELECT 客户ID FROM db_crm.单位客户信息表 where 手机号码=%s",(phone,))
        cardid_exist = self.cursor.execute("SELECT 客户ID FROM db_crm.单位客户信息表 where 身份证号码=%s",(cardid,))
        name_exist = self.cursor.execute("SELECT 客户ID FROM db_crm.单位客户信息表 where 姓名=%s",(name,))
        if phone_exist > 0 and cardid_exist > 0 and name_exist > 0:
            return 'none'
        else:
            sql = "UPDATE db_crm.单位客户信息表 SET 姓名 = %s , 手机号码 = %s, 首次体验金额 = %s, 首次体验时间 = %s, 身份证号码 = %s WHERE 手机号码 = %s"
            #self.cursor.execute("insert into db_crm.邮益宝客户数据录入表1 (姓名,身份证号码,手机号码,首次体验金额,单位名称) values ('张10','360109998765423132','13456765873','200','江西景航航空锻铸有限公司')")
            self.cursor.execute(sql,(name,phone,first_num,first_time,cardid,ygid))
            self.conn.commit()
            return 'OK'

    def get_company_list(self,rowcount,pagesize):
        '''获取单位列表信息'''

        rowcount = int(rowcount)
        pagesize = rowcount + int(pagesize)

        sql = "SELECT 单位ID,单位名称, 单位人数, 总资产,开办邮益宝人数,归属部门,归属网点 FROM db_crm.单位明细表 where 营销员ID = 'YUANGONG2018080002' order by 单位ID limit %s,%s"
        self.cursor.execute(sql,(rowcount,pagesize))
        results = []
        for r in self.cursor.fetchall():
            d = dict()
            d['dwid'] = r[0]
            d['dwmc'] = r[1]
            d['dwrs'] = r[2]
            d['zzc'] = str(r[3])
            d['kbyybrs'] = r[4]
            d['gsbm'] = r[5]
            d['gswd'] = r[6]
            results.append(d)       

        return results

    def get_company_list_a(self,name_find,rowcount,pagesize):
        '''获取单位列表信息'''

        rowcount = int(rowcount)
        pagesize = rowcount + int(pagesize)

        sql = "SELECT 单位ID,单位名称, 单位人数, 总资产,开办邮益宝人数,归属部门,归属网点 FROM db_crm.单位明细表 where 营销员ID = %s  order by 单位ID limit %s,%s"
        self.cursor.execute(sql,(name_find,rowcount,pagesize))
        results = []
        for r in self.cursor.fetchall():
            d = dict()
            d['dwid'] = r[0]
            d['dwmc'] = r[1]
            d['dwrs'] = r[2]
            d['zzc'] = str(r[3])
            d['kbyybrs'] = r[4]
            d['gsbm'] = r[5]
            d['gswd'] = r[6]
            results.append(d)       

        return results

    def get_company_more(self,yxyid,rowcount,pagesize):
        '''获取单位列表更多信息'''

        rowcount = int(rowcount) 
        pagesize = rowcount + int(pagesize)

        self.cursor.execute("SELECT 单位ID,单位名称, 单位人数, 总资产,开办邮益宝人数,归属部门,归属网点 FROM db_crm.单位明细表 where 营销员ID = %s order by 单位ID limit %s,%s",(yxyid,rowcount,pagesize))
        results = []
        for r in self.cursor.fetchall():
            d = dict()
            d['dwid'] = r[0]
            d['dwmc'] = r[1]
            d['dwrs'] = r[2]
            d['zzc'] = str(r[3])
            d['kbyybrs'] = r[4]
            d['gsbm'] = r[5]
            d['gswd'] = r[6]
            results.append(d)

        return results

    def get_company_inser(self,dwid,dwmc,dwrs,zzc,kbyybrs,gsbm,gswd):
        '''获取单位信息'''
        dwid = str(dwid)
        dwrs = str(dwrs)
        zzc = str(zzc)
        kbyybrs = str(kbyybrs)
        yxyid = 'YUANGONG2018080002'
        sql = "insert into db_crm.单位明细表 (单位ID,单位名称,单位人数,总资产,开办邮益宝人数,归属部门,归属网点,营销员ID) values (%s,%s,%s,%s,%s,%s,%s,%s)"
        self.cursor.execute(sql,(dwid,dwmc,dwrs,zzc,kbyybrs,gsbm,gswd,yxyid))
        self.conn.commit()

        return dwid


    def get_company_change_yxyid(self,yxyid,dwid):
        '''修改单位交接营销员ID'''

        sql = "UPDATE db_crm.单位明细表 SET 营销员ID = %s  WHERE 单位ID = %s"
        self.cursor.execute(sql,(yxyid,dwid))
        self.conn.commit()

        return 'OK'  